﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.SqlClient;
using System.Data;
using System.Collections;
using System.Web;
using System.Linq.Expressions;
using System.Text.RegularExpressions;

namespace lxwmvc
{
    /// <summary>
    /// 查询类和分页类
    /// </summary>
    public class queryhelper
    {
        /// <summary>
        /// 执行sql语句 返回object 第一行第一列信息
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public static object get(string sql)
        {
            return get<object>(sql);
        }


        static Regex TopRegex = new Regex(@"\s*select\s+top", RegexOptions.IgnoreCase);
        /// <summary>
        /// 得到一个实体对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static T get<T>(string sql = "", params object[] args)
        {
            var type = typeof(T);
            var _sql = sql;
            if (type != typeof(supermodel) && string.IsNullOrEmpty(_sql))
            {
                sql = "select top 1 * from " + type.Name.Substring(1);
            }

            //写后面很不爽
            if (type != typeof(supermodel) && _sql.StartsWith("where", StringComparison.OrdinalIgnoreCase))
            {
                sql = "select top 1 * from " + type.Name.Substring(1) + " " + _sql;
            }

            if (sql.Trim().StartsWith("select", StringComparison.OrdinalIgnoreCase))
            {
                int selectindex = sql.IndexOf("select", 0, StringComparison.OrdinalIgnoreCase);
                if (!TopRegex.IsMatch(sql) && selectindex != -1)
                {
                    sql = "select top 1 " + sql.Substring(selectindex + "select".Length);
                }
            }


            if (type.IsSubclassOf(typeof(supermodel)) || type == typeof(supermodel))
            {                
                return sqlhelper.query<T>(sql, args).FirstOrDefault();
            }
            else
                return sqlhelper.get<T>(sql, args);
        }


        ///// <summary>
        ///// 执行sql语句,返回datarows 信息
        ///// </summary>
        ///// <param name="sql">sql语句</param>
        ///// <param name="args"></param>
        ///// <returns></returns>
        //public static DataRowCollection rows(string sql, params object[] args)
        //{
        //    return sqlhelper.query(sql, args).totable().Rows;
        //}

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static int execute(string sql, params object[] args)
        {
            return sqlhelper.execute(sql, args);
        }

        /// <summary>
        /// DataRow dr 转换成supermodel
        /// </summary>
        /// <returns></returns>
        public static supermodel model(DataRow dr)
        {
            supermodel sm = supermodel.create();
            for (int i = 0; i < dr.Table.Columns.Count; i++)
            {
                var cname = dr.Table.Columns[i].ColumnName.ToLower();
                sm[cname] = dr[cname];
            }
            return sm;
        }

        ///// <summary>
        ///// 执行sql语句，返回model 信息
        ///// </summary>
        ///// <param name="sql"></param>
        ///// <returns></returns>
        //public static supermodel model(string sql, params object[] args)
        //{
        //    return sqlhelper.model(sql,args);
        //}
        ///// <summary>
        ///// 执行sql语句,返回supermodel List<LxwData>
        ///// </summary>
        ///// <param name="sql">sql语句</param>
        ///// <returns></returns>
        //public static List<supermodel> list(string sql, params object[] args)
        //{
        //    return list(sqlhelper.query(sql,args).totable());
        //}

        ///// <summary>
        ///// 返回信息
        ///// </summary>       
        ///// <returns></returns>
        //public static List<supermodel> list(DataTable dt)
        //{
        //    if (dt == null) return null;
        //    List<supermodel> lst = new List<supermodel>();

            
        //    DataRowCollection rows = dt.Rows;
        //    foreach (DataRow dr in rows)
        //    {
        //        lst.Add(model(dr));                
        //    }

        //    return lst;
        //}

        ///// <summary>
        ///// 执行sql语句,得到一条DataRow信息
        ///// </summary>
        ///// <param name="sql">sql语句</param>
        ///// <returns></returns>
        ////public static DataRow row(string sql, params object[] args)
        ////{
        ////    DataTable dt = sqlhelper.query(sql,args).totable();
        ////    if (dt.Rows.Count == 0)
        ////        return null;

        ////    return dt.Rows[0];            
        ////}

        /// <summary>
        /// 查询内容
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static IEnumerable<T> query<T>(string sql = "", params object[] args) //where T:supermodel,new()
        {
            var type = typeof(T);
            var _sql = sql;
            if (type != typeof(supermodel) && string.IsNullOrEmpty(_sql))
            {
                sql = "select * from " + type.Name.Substring(1);
            }

            if (type != typeof(supermodel) && _sql.StartsWith("where", StringComparison.OrdinalIgnoreCase))
            {
                sql = "select * from " + type.Name.Substring(1) + " "+ _sql;
            }


            return sqlhelper.query<T>(sql, args);
        }

        /// <summary>
        /// 执行CONN方法 有返回值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="fun"></param>
        /// <returns></returns>
        public static T FUN<T>(Func<IDbConnection, IDbTransaction, T> fun)
        {
            return sqlhelper.FUN<T>(fun);
        }
        /// <summary>
        /// 执行CONN方法 无返回值
        /// </summary>
        /// <param name="fun"></param>
        public static void FUN(Action<IDbConnection, IDbTransaction> fun)
        {
            sqlhelper.FUN(fun);
        } 

        /// <summary>
        /// 查询内容
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static IEnumerable<dynamic> query(string sql, params object[] args)
        {
            return sqlhelper.query(sql, args);
        }
       
        /// <summary>
        /// 实现分页，跟原来分页相同
        /// </summary>
        /// <param name="strtablename">组合的表名</param>
        /// <param name="strfileds">查询的字段名称</param>
        /// <param name="strorderfileds">排序</param>        
        /// <param name="ipageindex">当前页码</param>
        /// <param name="ipagesize">每页数量</param>
        /// <param name="strwhere">查询条件</param>
        /// <param name="args">预编译参数</param>
        /// <returns></returns>
        public static pagerinfo pager(string strtablename, string strfileds, string strorderfileds, object ipageindex, object ipagesize, string strwhere = "", params object[] args)
        {
            return sqlhelper.showpager(strtablename
                , strfileds
                , strorderfileds
                , ipageindex.toint32(1)
                , ipagesize.toint32(10)
                , strwhere, args
                );
        }

        /// <summary>
        /// 执行sql语句分页
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ipageindex"></param>
        /// <param name="ipagesize"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static pagerinfo pager(string sql, object ipageindex, object ipagesize, params object[] args)
        {
            return sqlhelper.pager(sql, ipageindex, ipagesize, args);
        }

        ///// <summary>
        ///// 判断某一个字段是否在数据库唯一性
        ///// </summary>
        ///// <param name="table">表名</param>
        ///// <param name="column">唯一性字段</param>
        ///// <param name="value">唯一性字段对应的值</param>
        ///// <param name="msg">错误提示</param>
        ///// <param name="keycolumn">主键</param>
        ///// <param name="keyvalue">主键对应的值</param>
        ///// <param name="where">外部where条件</param>
        //public static void weiyixing(string table, string column, string value, string msg, string keycolumn = "", string keyvalue = "", string where = "")
        //{
        //    string sql = "select count(1) from {0} where {1}='{2}' {3} {4}";

        //    if (where != "") where = " and " + where;

        //    if (string.IsNullOrEmpty(keycolumn) || string.IsNullOrEmpty(keyvalue))
        //        sql = string.Format(sql, table, column, value, "", where);
        //    else
        //        sql = string.Format(sql, table, column, value, " and " + keycolumn + "<>" + keyvalue, where);

        //    int count = sqlhelper.get<int>(sql);
        //    if (count > 0) throw new Exception(msg);

        //}
    }
}
